/**
 * 2018年11月26日下午3:50:41
 * @author 谢进财
 */
package com.baihang.project.ds.service;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;

import com.baihang.framework.dao.Record;
import com.baihang.framework.kit.DownLoadKit;
import com.baihang.framework.kit.ExcelKit;
import com.baihang.framework.kit.JsonKit;
import com.baihang.framework.kit.PathKit;
import com.baihang.framework.kit.StrKit;
import com.baihang.framework.service.BaseService;
import com.baihang.project.ds.dao.FangYuanDao;

import net.sf.json.JSONArray;
@Service
public class FangYuanService extends BaseService {
	private static Logger logger = LoggerFactory.getLogger(FangYuanService.class);
	@Autowired
	private FangYuanDao dao;
	
	public String getGrid(int page,int pagesize,String sortname,String sortorder,String where) {
		int count=0;
		List<Record> list=null;
		if(StrKit.isNotEmpty(where) && !"全部楼栋".equals(where)) {
			count = dao.queryForInt(String.format("select count(*) from room where D_BanName='%s'", where));
			list = dao.queryForRecordList(String.format("select * from room where D_BanName='%s' order by %s %s limit %d,%d ",where,sortname,sortorder,(page-1)*pagesize,pagesize));
		}else {
			count = dao.queryForInt("select count(*) from room");
			list = dao.queryForRecordList(String.format("select * from room order by %s %s limit %d,%d ", sortname,sortorder,(page-1)*pagesize,pagesize));
		}
		String jsonRecord = JsonKit.listToJson(list, 1);
		StringBuilder sb = new StringBuilder("{");
		sb.append("\"Rows\":"+jsonRecord+",");
		sb.append("\"Total\":"+count);
		sb.append("}");
		
		return sb.toString();
	}
	
	public boolean saveGrid(String json) {
		JSONArray jsonArray = JSONArray.fromObject(json); 
		dao.batchUpdateFangYuanList(jsonArray);
		return true;
	}
	
	
	public boolean addGrid(String json,String banname,int nodeId,String nodeType,String nodeOrder) {
		logger.info("banname="+banname);
		JSONArray jsonArray = JSONArray.fromObject(json); 
		try {
			dao.batchInsertFangYuanList(jsonArray, banname,nodeId,nodeType,nodeOrder);
		}catch (Exception e) {
			logger.info(e.getMessage());
			return false;
		}
		return true;
	}
	
	public ResponseEntity<byte[]> downloadFangYuanByLouDong(String louDongName) throws Exception{
		List<Record> records = dao.queryForRecordList(String.format("select D_ID as 序号,D_RoomNo as 室号,D_Unit as 单元,D_FloorArea as 建筑面积,D_InnerArea as 室内面积,D_AssessmentArea as 分摊面积,D_Price as 原单价,D_Total as 原总价,D_discountPrice as 折后单价,D_discountTotal as 折后总价,D_column as 对应列,d_position as 单元名称,D_HouseModel as 户型,D_RoomType as 类型,D_Monitor as 内控,D_CollectionCount as 假收藏,E_CollectionCount as 真收藏 from room where D_BanName='%s'", louDongName));		
		HSSFWorkbook workbook = ExcelKit.writeToExcel(records);
				
		return DownLoadKit.exportExcel(workbook, louDongName);
	}
	
	public ResponseEntity<byte[]> downloadFangYuanForDianShi() throws Exception{
		List<Record> records = dao.queryForRecordList("select D_ID as 序号,D_RoomNo as 室号,D_Unit as 单元,D_FloorArea as 建筑面积,D_InnerArea as 室内面积,D_AssessmentArea as 分摊面积,D_Price as 原单价,D_Total as 原总价,D_discountPrice as 折后单价,D_discountTotal as 折后总价,D_column as 对应列,d_position as 单元名称,D_HouseModel as 户型,D_RoomType as 类型 from room");
		
		HSSFWorkbook workbook = ExcelKit.writeToExcel(records);
		
		return DownLoadKit.exportExcel(workbook, "房源");
	}
	
	public String readFangYuanByExcel(String fileName) {
		String filePath = PathKit.getWebRootPath()+"/upload/"+fileName;
		System.err.println(filePath);
		File file = new File(filePath);
		 Map<Integer,String> keyMap = new HashMap<Integer,String>();			
		 keyMap.put(0, "D_ID");
		 keyMap.put(1, "D_RoomNo");
		 keyMap.put(2, "D_Unit");
		 keyMap.put(3, "D_FloorArea");
		 keyMap.put(4, "D_InnerArea");
		 keyMap.put(5, "D_AssessmentArea");
		 keyMap.put(6, "D_Price");
		 keyMap.put(7, "D_Total");
		 keyMap.put(8, "D_discountPrice");
		 keyMap.put(9, "D_discountTotal");
		 keyMap.put(10, "D_column");
		 keyMap.put(11, "d_position");
		 keyMap.put(12, "D_HouseModel");
		 keyMap.put(13, "D_RoomType");
		 keyMap.put(14, "D_Monitor");
		 keyMap.put(15, "D_CollectionCount");
		 keyMap.put(16, "E_CollectionCount");
		 List<Record> recList = new ArrayList<Record>();
		try {
			recList = ExcelKit.readFromExcelForString(file,keyMap);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		/*for(Record rec:recList) {
			rec.getColumns().put("D_BanName", fileName.substring(0, fileName.indexOf(".")));
		}*/
		 String json =String.format("{\"Rows\":%s}", JsonKit.listToJson(recList, 1));
		 //System.err.println(json);
		return json;
	}
	
	public void downFangYuan2(HttpServletResponse response) throws Exception {
			
		/*HSSFWorkbook wb = new HSSFWorkbook();                     // 创建工作簿对象  
	    HSSFSheet sheet = wb.createSheet("sheet1"); //创建一个列表
	    sheet.setColumnWidth(0, 4000);//设置列的宽度 第一个参数表示位置
	    
	    //设置标头
	    HSSFRow rowm = sheet.createRow(0);  //创建行
	    rowm.createCell(0).setCellValue("序号2"); 
	    
	    HttpKit.exportExcel2(response, wb, "谢煜恒");*/
		 String filePath ="E:\\Test\\张美林.xls";
		    File file = new File(filePath);
		DownLoadKit.downLoadFile2(response, file);
	}
	
	public boolean deleteRoom(String banname) {
		String sql ="delete from room";
		if(!"全部".equals(banname)) {
			sql =String.format("delete from room  where D_BanName='%s'", banname);
		}
		int result = dao.delete(sql);			
		if(result>0) {
			return true;
		}
		return false;
	}
	
}
